import os
import io
import logging
from datetime import datetime

from celery.worker.control import heartbeat
from users.models import *
from openpyxl import workbook
from encourage.models import *
from merchants.models import *
from activities.models import Channel
from django.db import transaction
from openpyxl import load_workbook
from django.http import JsonResponse
from django.http import HttpResponse
from openpyxl.workbook import Workbook
from openpyxl.utils import get_column_letter
from rest_framework.viewsets import ModelViewSet
from django.views.decorators.csrf import csrf_exempt
from rest_framework.decorators import action
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from django.conf import settings
from django.contrib.auth.decorators import permission_required
from django.core.exceptions import ObjectDoesNotExist
from django.db.models.signals import pre_save, post_save
from django.dispatch import receiver
import logging
# 配置日志
logger = logging.getLogger(__name__)


class EncourageModelViewSet(ModelViewSet):
    """接收鼓励金活动"""
    def import_encourage_activity(self, request):
        """从Excel导入数据到鼓励金活动模型"""

        if request.method != 'POST':
            return JsonResponse({'status': 'error', 'message': '仅支持POST请求'})

        if 'file' not in request.FILES:
            return JsonResponse({'status': 'error', 'message': '未上传文件'})

        excel_file = request.FILES['file']

        # 检查文件类型
        if not excel_file.name.endswith(('.xlsx', '.xls')):
            return JsonResponse({'success': False, 'message': '请上传Excel文件(.xlsx或.xls)'}, status=400)

        # 定义表头与模型字段的映射
        expected_headers = {
            'id': 'id',
            'activity_id': 'activity_id',
            'name': 'name',
            'user': 'user_id',
            'file': 'file',
            'remark': 'remark',
            'create_time': 'create_time',
            'is_enter': 'is_enter',
            'max_coupons': 'max_coupons',
            'entry_coupons': 'entry_coupons',
            'coupons': 'coupons',
            'pay_way': 'pay_way',
            'is_auth': 'is_auth',
            'destroy_user': 'destroy_user_id',
            'channel': 'channel_id',
            'publish_start_time': 'publish_start_time',
            'publish_end_time': 'publish_end_time',
            'level': 'level_id',
            'is_keguan': 'is_keguan',
            'pushEventIds': 'pushEventIds'
        }
        # 定义需要转换为整数的字段
        int_fields = ['id', 'user_id', 'destroy_user_id', 'channel_id', 'level_id',
                      'is_enter', 'max_coupons', 'entry_coupons', 'coupons',
                      'pay_way', 'is_auth', 'is_keguan']

        # 定义需要转换为日期时间的字段
        datetime_fields = ['create_time', 'publish_start_time', 'publish_end_time']

        try:
            # 使用openpyxl加载Excel文件
            wb = load_workbook(excel_file, data_only=True)
            ws = wb.active

            # 获取表头
            headers = {}
            for col_idx, cell in enumerate(ws[1], 1):
                if cell.value in expected_headers:
                    headers[cell.value] = col_idx

            # 验证必要的表头是否存在
            required_headers = ['name']  # 至少需要name字段
            missing_headers = [h for h in required_headers if h not in headers]
            if missing_headers:
                return JsonResponse({
                    'success': False,
                    'message': f'缺少必要的表头: {", ".join(missing_headers)}'
                }, status=400)

            # 准备导入的数据
            import_data = []
            row_errors = []

            # 从第二行开始读取数据
            for row_idx, row in enumerate(ws.iter_rows(min_row=2, values_only=True), 2):
                row_data = {}
                row_valid = True
                row_error_msg = []

                for header_name, col_idx in headers.items():
                    field_name = expected_headers[header_name]
                    value = row[col_idx - 1] if col_idx - 1 < len(row) else None

                    # 处理空值
                    if value is None or value == '':
                        if field_name in int_fields:
                            row_data[field_name] = 0 if field_name in ['max_coupons', 'entry_coupons',
                                                                       'coupons'] else None
                        elif field_name in datetime_fields:
                            row_data[field_name] = None
                        else:
                            row_data[field_name] = None
                        continue

                    # 处理整数类型字段
                    if field_name in int_fields:
                        try:
                            if isinstance(value, str) and value.strip() == '':
                                row_data[field_name] = None
                            else:
                                row_data[field_name] = int(value)
                        except (ValueError, TypeError) as e:
                            row_valid = False
                            row_error_msg.append(f'字段"{header_name}"的值"{value}"不是有效的整数')

                    # 处理日期时间类型字段（只使用parse_datetime方法）
                    elif field_name in datetime_fields:
                        parsed_value = self.parse_datetime(value)
                        if parsed_value is None:
                            row_valid = False
                            row_error_msg.append(f'字段"{header_name}"的值"{value}"不是有效的日期时间格式')
                        else:
                            row_data[field_name] = parsed_value

                    # 其他字段直接赋值
                    else:
                        row_data[field_name] = value

                # 如果行数据有效，添加到导入列表
                if row_valid:
                    import_data.append(row_data)
                else:
                    row_errors.append({
                        'row': row_idx,
                        'errors': row_error_msg
                    })

            # 如果有错误，返回错误信息
            if row_errors:
                return JsonResponse({
                    'success': False,
                    'message': f'发现{len(row_errors)}行数据有错误',
                    'errors': row_errors
                }, status=400)

            # 如果没有数据可导入
            if not import_data:
                return JsonResponse({'success': False, 'message': '没有有效的数据可导入'}, status=400)

            # 开始事务
            with transaction.atomic():
                created_count = 0
                updated_count = 0

                for data in import_data:
                    # 检查是否有ID，有则更新，无则创建
                    if 'id' in data and data['id'] is not None:
                        try:

                            activity = EncourageActivity.objects.get(id=data['id'])
                            # 更新现有记录
                            for field, value in data.items():
                                setattr(activity, field, value)
                            activity.save()
                            updated_count += 1
                        except ObjectDoesNotExist:
                            # ID不存在，创建新记录并保留ID
                            EncourageActivity.objects.create(**data)
                            created_count += 1
                    else:
                        # 创建新记录（如果没有ID，Django会自动生成）
                        EncourageActivity.objects.create(**data)
                        created_count += 1

                return JsonResponse({
                    'success': True,
                    'message': f'数据导入成功，创建{created_count}条记录，更新{updated_count}条记录'
                })

        except Exception as e:
            logger.error(f'导入Excel文件时出错: {str(e)}', exc_info=True)
            return JsonResponse({'success': False, 'message': f'导入失败: {str(e)}'}, status=400)



    """接收鼓励金活动-领取明细数据"""
    def import_encourage_data(self,request):
        """从Excel导入数据到EncourageDraw模型，存在则跳过，不存在则创建"""

        if request.method != 'POST':
            return JsonResponse({'status': 'error', 'message': '仅支持POST请求'})

        if 'file' not in request.FILES:
            return JsonResponse({'status': 'error', 'message': '未上传文件'})

        excel_file = request.FILES['file']

        # 检查文件类型
        if not excel_file.name.endswith(('.xlsx', '.xls')):
            return JsonResponse({'success': False, 'message': '请上传Excel文件(.xlsx或.xls)'}, status=400)

        # 定义表头与模型字段的映射，注意外键字段对应ID列名
        expected_headers = {
            'id': 'id',
            'business_no': 'business_no',
            'amount': 'amount',
            'partner_trade_no': 'partner_trade_no',
            'desc': 'desc',
            'payment_no': 'payment_no',
            'payment_time': 'payment_time',
            'payment_status': 'payment_status',
            'nonce_str': 'nonce_str',
            'activity_id': 'activity',  # 外键字段，Excel中是ID，模型中是对象
            'level_id': 'level',  # 外键字段，Excel中是ID，模型中是对象
            'merchant_user_id': 'merchant_user',  # 外键字段，Excel中是ID，模型中是对象
            'return_msg': 'return_msg',
            'encouragemerchant_id': 'encouragemerchant_id',
            'account': 'account',
            'name': 'name',
            'payment_type': 'payment_type',
            'pay_way': 'pay_way',
            'destroy_user_id': 'destroy_user',  # 外键字段，Excel中是ID，模型中是对象
            'channel_id': 'channel',  # 外键字段，Excel中是ID，模型中是对象
            'pay_order_no': 'pay_order_no',
            'label': 'label',
        }

        # 定义需要转换为整数的字段
        int_fields = ['id', 'payment_status', 'payment_type', 'pay_way', 'label', 'destroy_user_id', 'channel_id',
                      'level_id', 'merchant_user_id']

        # 定义需要转换为日期时间的字段
        datetime_fields = ['payment_time']

        try:
            # 使用openpyxl加载Excel文件
            wb = load_workbook(excel_file, data_only=True)
            ws = wb.active

            # 获取表头
            headers = {}
            for col_idx, cell in enumerate(ws[1], 1):
                if cell.value in expected_headers:
                    headers[cell.value] = col_idx

            # 验证必要的表头是否存在
            required_headers = ['id']  # 至少需要id字段
            missing_headers = [h for h in required_headers if h not in headers]
            if missing_headers:
                return JsonResponse({
                    'success': False,
                    'message': f'缺少必要的表头: {", ".join(missing_headers)}'
                }, status=400)

            # 准备导入的数据
            import_data = []
            row_errors = []

            # 临时存储已存在的ID，避免重复查询数据库
            existing_ids = set()

            # 从第二行开始读取数据
            for row_idx, row in enumerate(ws.iter_rows(min_row=2, values_only=True), 2):
                row_data = {}
                row_valid = True
                row_error_msg = []

                for header_name, col_idx in headers.items():
                    field_name = expected_headers[header_name]
                    value = row[col_idx - 1] if col_idx - 1 < len(row) else None

                    # 处理空值
                    if value is None or value == '':
                        if field_name in int_fields:
                            row_data[field_name] = 0 if field_name in ['payment_status', 'payment_type',
                                                                       'pay_way'] else None
                        elif field_name in datetime_fields:
                            row_data[field_name] = None
                        else:
                            row_data[field_name] = None
                        continue

                    # 处理整数类型字段
                    if field_name in int_fields:
                        try:
                            if isinstance(value, str) and value.strip() == '':
                                row_data[field_name] = None
                            else:
                                row_data[field_name] = int(value)
                        except (ValueError, TypeError) as e:
                            row_valid = False
                            row_error_msg.append(f'字段"{header_name}"的值"{value}"不是有效的整数')

                    # 处理日期时间类型字段
                    elif field_name in datetime_fields:
                        parsed_value = self.parse_datetime(value)
                        if parsed_value is None:
                            row_valid = False
                            row_error_msg.append(f'字段"{header_name}"的值"{value}"不是有效的日期时间格式')
                        else:
                            row_data[field_name] = parsed_value

                    # 处理外键字段，通过ID获取对象
                    elif field_name in ['merchant_user', 'activity', 'level', 'destroy_user', 'channel']:
                        if value is not None:
                            try:
                                if field_name == 'merchant_user':
                                    row_data[field_name] = MerchantUser.objects.get(id=value)
                                elif field_name == 'activity':
                                    row_data[field_name] = EncourageActivity.objects.get(id=value)
                                elif field_name == 'level':
                                    row_data[field_name] = Level.objects.get(id=value)
                                elif field_name == 'destroy_user':
                                    row_data[field_name] = User.objects.get(id=value)
                                elif field_name == 'channel':
                                    row_data[field_name] = Channel.objects.get(id=value)
                            except ObjectDoesNotExist:
                                row_valid = False
                                row_error_msg.append(f'字段"{header_name}"对应的ID {value} 在数据库中不存在')
                        else:
                            row_data[field_name] = None

                    # 其他字段直接赋值
                    else:
                        row_data[field_name] = value

                if row_valid:
                    id_value = row_data['id']
                    if id_value in existing_ids:
                        continue
                    try:
                        EncourageDraw.objects.get(id=id_value)
                        existing_ids.add(id_value)
                        continue
                    except ObjectDoesNotExist:
                        import_data.append(row_data)
                else:
                    row_errors.append({
                        'row': row_idx,
                        'errors': row_error_msg
                    })

            # 如果有错误，返回错误信息
            if row_errors:
                return JsonResponse({
                    'success': False,
                    'message': f'发现{len(row_errors)}行数据有错误',
                    'errors': row_errors
                }, status=400)

            # 如果没有数据可导入
            if not import_data:
                return JsonResponse({'success': False, 'message': '没有有效的数据可导入'}, status=400)

            # 禁用信号处理
            @receiver(pre_save, sender=EncourageDraw)
            def disable_pre_save(sender, **kwargs):
                pass

            @receiver(post_save, sender=EncourageDraw)
            def disable_post_save(sender, **kwargs):
                pass

            pre_save.disconnect(disable_pre_save, sender=EncourageDraw)
            post_save.disconnect(disable_post_save, sender=EncourageDraw)

            batch_size = 1000
            created_count = 0
            skipped_count = len(existing_ids)

            try:
                with transaction.atomic():
                    for i in range(0, len(import_data), batch_size):
                        batch = import_data[i:i + batch_size]
                        EncourageDraw.objects.bulk_create([EncourageDraw(**data) for data in batch])
                        created_count += len(batch)
            except Exception as e:
                logger.error(f'批量创建数据时出错: {str(e)}', exc_info=True)
                return JsonResponse({'success': False, 'message': f'导入失败: {str(e)}'}, status=400)
            finally:
                # 恢复信号处理
                pre_save.connect(disable_pre_save, sender=EncourageDraw)
                post_save.connect(disable_post_save, sender=EncourageDraw)

            return JsonResponse({
                'success': True,
                'message': f'数据导入成功，创建{created_count}条记录，跳过{skipped_count}条记录'
            })

        except Exception as e:
            logger.error(f'导入Excel文件时出错: {str(e)}', exc_info=True)
            return JsonResponse({'success': False, 'message': f'导入失败: {str(e)}'}, status=400)








    @classmethod
    def parse_datetime(cls, value):
        """解析日期时间字段"""
        if not value:
            return None

        try:
            # 尝试多种格式解析，包括带微秒的格式
            if isinstance(value, str):
                for fmt in [
                    '%Y-%m-%d %H:%M:%S.%f',  # 带微秒的格式
                    '%Y-%m-%d %H:%M:%S',     # 标准格式
                    '%Y/%m/%d %H:%M:%S',     # Excel常用格式
                    '%Y-%m-%d',              # 只有日期
                    '%Y/%m/%d'               # Excel日期格式
                ]:
                    try:
                        return datetime.strptime(value, fmt)
                    except ValueError:
                        continue
            elif isinstance(value, datetime):
                return value

            logger.warning(f"无法解析的日期时间格式: {value}")
            return None

        except Exception as e:
            logger.error(f"解析日期时间失败: {value}, 错误: {str(e)}")
            return None
